package com.message.tcp.ip.socket.configure.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.message.tcp.ip.socket.configure.entity.CardAccount;
import com.message.tcp.ip.socket.util.MessageDefinition;
import com.message.tcp.ip.socket.util.SqliteConnection;
@Repository("cardAccountDao")
public class CardAccountDaoImp implements CardAccountDao {

	@Override
	public void save(CardAccount cardAcct) throws SQLException {
		Connection conn = SqliteConnection.getConnection();
		String sql = "INSERT INTO CARD_ACCOUNT (CARDNUMBER, EXPIRYDATE, CAV, AMOUNTTYPE, BALANCE, CURRENCY, FLAG, BIN) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setString(1, cardAcct.getCard());
		stat.setString(2, cardAcct.getExpDate());
		stat.setString(3, cardAcct.getCav());
		stat.setString(4, cardAcct.getAmtType());
		stat.setDouble(5, cardAcct.getBal());
		stat.setString(6, cardAcct.getCurr());
		stat.setString(7, cardAcct.getFlag());
		stat.setString(8, cardAcct.getBin());
		stat.execute();
	}

	@Override
	public List<CardAccount> getCardAccount() throws SQLException {
		String sql = "SELECT CARDID AS ID, CARDNUMBER AS CARD, EXPIRYDATE AS EXPDATE, CAV, AMOUNTTYPE AS AMTTYPE, BALANCE, CURRENCY, FLAG, BIN FROM CARD_ACCOUNT";
		Connection conn = SqliteConnection.getConnection();
		PreparedStatement stat = conn.prepareStatement(sql);
		ResultSet rs = stat.executeQuery();
		List<CardAccount> cardAccts = new ArrayList<CardAccount>();
		while(rs.next()){
			int id = rs.getInt("ID");
			String card = rs.getString("CARD");
			String expDate = rs.getString("EXPDATE");
			String cav = rs.getString("CAV");
			String amtType = rs.getString("AMTTYPE");
			double bal = rs.getDouble("BALANCE");
			String curr = rs.getString("CURRENCY");
			String flag = rs.getString("FLAG");
			String bin = rs.getString("BIN");
			CardAccount cardAcct = new CardAccount();
			cardAcct.setId(id);
			cardAcct.setCard(card);
			cardAcct.setExpDate(expDate);
			cardAcct.setCav(cav);
			cardAcct.setAmtType(amtType);
			cardAcct.setBal(bal);
			cardAcct.setCurr(curr);
			cardAcct.setFlag(flag);
			cardAcct.setBin(bin);
			cardAccts.add(cardAcct);
		}
		return cardAccts;
	}

	@Override
	public void update(CardAccount cardAcct) throws SQLException {
		String sql = "UPDATE CARD_ACCOUNT SET CARDNUMBER = ?, EXPIRYDATE = ?, CAV = ?, AMOUNTTYPE = ?, CURRENCY = ?, BALANCE = ?, FLAG = ? WHERE CARDID = ?";
		Connection conn = SqliteConnection.getConnection();
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setString(1, cardAcct.getCard());
		stat.setString(2, cardAcct.getExpDate());
		stat.setString(3, cardAcct.getCav());
		stat.setString(4, cardAcct.getAmtType());
		stat.setString(5, cardAcct.getCurr());
		stat.setDouble(6, cardAcct.getBal());
		stat.setString(7, cardAcct.getFlag());
		stat.setInt(8, cardAcct.getId());
		stat.execute();
	}
	@Override
	public CardAccount getCardAccount(CardAccount ca) throws SQLException {
		String sql = "SELECT CARDID AS ID, CARDNUMBER AS CARD, EXPIRYDATE AS EXPDATE, CAV, AMOUNTTYPE AS AMTTYPE, BALANCE, CURRENCY, FLAG, BIN FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		Connection conn = SqliteConnection.getConnection();
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setString(1, ca.getCard());
		ResultSet rs = stat.executeQuery();
		CardAccount cardAcct = null;
		while(rs.next()){
			int id = rs.getInt("ID");
			String card = rs.getString("CARD");
			String expDate = rs.getString("EXPDATE");
			String cav = rs.getString("CAV");
			String amtType = rs.getString("AMTTYPE");
			double bal = rs.getDouble("BALANCE");
			String curr = rs.getString("CURRENCY");
			String flag = rs.getString("FLAG");
			String bin = rs.getString("BIN");
			cardAcct = new CardAccount();
			cardAcct.setId(id);
			cardAcct.setCard(card);
			cardAcct.setExpDate(expDate);
			cardAcct.setCav(cav);
			cardAcct.setAmtType(amtType);
			cardAcct.setBal(bal);
			cardAcct.setCurr(curr);
			cardAcct.setFlag(flag);
			cardAcct.setBin(bin);
		}
		return cardAcct;
	}
	@Override
	public String getExpiryDate(String card){
		String sql = "SELECT EXPIRYDATE FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		String exp = "";
		Connection conn = SqliteConnection.getConnection();
		PreparedStatement stat;
		try {
			stat = conn.prepareStatement(sql);
			stat.setString(1, card);
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				exp = rs.getString("EXPIRYDATE");
			}
		} catch (SQLException e) {
			MessageDefinition.logger.debug("Exception occur while try to retreiv data from data element table", e);
		}
		return exp;
	}
	@Override
	public String getCav(String card){
		String sql = "SELECT CAV FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		Connection conn = SqliteConnection.getConnection();
		String cav = "";
		try {
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1, card);
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				cav = rs.getString("CAV");
			}
		} catch (SQLException e) {
			MessageDefinition.logger.debug("Exception occur while try to retreiv data from data element table", e);
		}
		return cav;
	}
	@Override
	public String getBalance(String card){
		String sql = "SELECT BALANCE AS AMT FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		Connection conn = SqliteConnection.getConnection();
		int val = 0;
		double num = 0;
		try{
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1, card);
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				num = rs.getDouble("AMT");
			}
			val = (int) (num*100);
		}
		catch(SQLException e){
			MessageDefinition.logger.debug("Exception occur while try to retreiv data from data element table", e);
		}
		return MessageDefinition.integerFormater(val,"000000000000");
	}
	@Override
	public String getFlag(String card){
		String sql = "SELECT FLAG AS FG FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		Connection conn = SqliteConnection.getConnection();
		String val = "";
		try{
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1, card);
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				val = rs.getString("FG");
			}
			conn.close();
		}
		catch(SQLException e){
			MessageDefinition.logger.debug("Exception occur while try to retreiv data from data element table", e);
		}
		return val;
	}
	@Override
	public String getCurrency(String card){
		String sql = "SELECT CURRENCY AS CURR FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		Connection conn = SqliteConnection.getConnection();
		String val = "";
		try{
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1, card);
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				val = rs.getString("CURR");
			}
		}
		catch(SQLException e){
			MessageDefinition.logger.debug("Exception occur while try to retreiv data from data element table", e);
		}
		return val;
	}
	@Override
	public String getAmtType(String card){
		String sql = "SELECT AMOUNTTYPE FROM CARD_ACCOUNT WHERE CARDNUMBER = ?";
		Connection conn = SqliteConnection.getConnection();
		String val = "";
		try{
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1, card);
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				val = rs.getString("AMOUNTTYPE");
			}
		}
		catch(SQLException e){
			MessageDefinition.logger.debug("Exception occur while try to retreiv data from data element table", e);
		}
		return val;
	}
	@Override
	public String getCardNumber() throws SQLException{
		String sql = "SELECT CARDNUMBER AS CARD FROM CARD_ACCOUNT";
		Connection conn = SqliteConnection.getConnection();
		String val = "";
		PreparedStatement stat = conn.prepareStatement(sql);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			val = rs.getString("CARD");
		}
		return val;
	}
	@Override
	public String getCardNumber(CardAccount cardAcct){
		String sql = "SELECT CARDNUMBER AS CARD FROM CARD_ACCOUNT WHERE CARDID = ?";
		Connection conn = SqliteConnection.getConnection();
		String val = "";
		try{
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setInt(1, cardAcct.getId());
			ResultSet rs = stat.executeQuery();
			while(rs.next()){
				val = rs.getString("CARD");
			}
		}
		catch(SQLException e){
			MessageDefinition.logger.debug("Exception occured while try to retrieve card number", e);
		}
		return val;
	}

}
